/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * ViewEmployeeInfo.java
 *
 * Created on 11 2, 11, 8:35:08 PM
 */

/**
 *
 * @author kylle
 */

import net.proteanit.sql.DbUtils;
import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

public class ViewEmployeeSalary extends javax.swing.JFrame {

    ViewEmployeeInfo vi = new ViewEmployeeInfo();

    public ViewEmployeeSalary() {
        initComponents();
        this.setBounds(400, 100, 850, 350);
        this.setTitle("Employee Salary Information");
        df = new SimpleDateFormat("yyyy-MM-dd");
    }

   PreparedStatement pst = null;
   ResultSet rs = null;
   Connection conn = null;
   Statement stat1;
   DateFormat df;

    private void UpdateJTable(){
    String sql = "SELECT *, FORMAT(BaseSalary-SSS-PagIBIGLoan-PagIBIGContract,2) as TotalSalary FROM salaryinfo";
    try{
        pst = conn.prepareStatement(sql);
        rs = pst.executeQuery();
        txtDateReceived.setDateFormatString("yyyy-MM-dd");
        jTable2.setModel(DbUtils.resultSetToTableModel(rs));
    }
    catch (Exception e){
        JOptionPane.showMessageDialog(null, e);
    }
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jScrollPane1 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();
        jLabel2 = new javax.swing.JLabel();
        jScrollPane2 = new javax.swing.JScrollPane();
        jTable2 = new javax.swing.JTable();
        cancelBtn = new javax.swing.JButton();
        updateBtn = new javax.swing.JButton();
        removeBtn = new javax.swing.JButton();
        viewInactiveBtn = new javax.swing.JButton();
        txtID = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();
        jLabel7 = new javax.swing.JLabel();
        jLabel6 = new javax.swing.JLabel();
        txtpagIBIGContract = new javax.swing.JTextField();
        jLabel8 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        txtBaseSalary = new javax.swing.JTextField();
        jLabel5 = new javax.swing.JLabel();
        jLabel9 = new javax.swing.JLabel();
        txtFName = new javax.swing.JLabel();
        txtLName = new javax.swing.JLabel();
        txtSSS = new javax.swing.JTextField();
        txtpagIBIGLoan = new javax.swing.JTextField();
        jLabel1 = new javax.swing.JLabel();
        txtDateReceived = new com.toedter.calendar.JDateChooser();

        jTable1.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Title 1", "Title 2", "Title 3", "Title 4"
            }
        ));
        jScrollPane1.setViewportView(jTable1);

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setResizable(false);
        addWindowListener(new java.awt.event.WindowAdapter() {
            public void windowOpened(java.awt.event.WindowEvent evt) {
                formWindowOpened(evt);
            }
        });

        jLabel2.setText("Employee Salary Information");

        jTable2.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null, null}
            },
            new String [] {
                "Employee ID", "First Name", "Last Name", "Base Salary", "SSS", "Pag-IBIG Loan", "Pag-IBIG Contract", "Date Received"
            }
        ));
        jTable2.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jTable2MouseClicked(evt);
            }
        });
        jScrollPane2.setViewportView(jTable2);

        cancelBtn.setText("Cancel");
        cancelBtn.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                cancelBtnMouseClicked(evt);
            }
        });

        updateBtn.setText("Update");
        updateBtn.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                updateBtnMouseClicked(evt);
            }
        });
        updateBtn.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                updateBtnActionPerformed(evt);
            }
        });

        removeBtn.setText("Remove");
        removeBtn.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                removeBtnMouseClicked(evt);
            }
        });

        viewInactiveBtn.setText("View Inactive");
        viewInactiveBtn.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                viewInactiveBtnMouseClicked(evt);
            }
        });
        viewInactiveBtn.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                viewInactiveBtnActionPerformed(evt);
            }
        });

        txtID.setText("0");

        jLabel3.setText("Employee ID:");

        jLabel7.setText("Pag-IBIG Loan:");

        jLabel6.setText("SSS:");

        jLabel8.setText("Pag-IBIG Contract:");

        jLabel4.setText("First Name:");

        jLabel5.setText("Base Salary:");

        jLabel9.setText("Last Name:");

        txtFName.setText("****");

        txtLName.setText("****");

        txtSSS.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtSSSActionPerformed(evt);
            }
        });

        txtpagIBIGLoan.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtpagIBIGLoanActionPerformed(evt);
            }
        });

        jLabel1.setText("Date Received:");

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGap(284, 284, 284)
                        .addComponent(jLabel2))
                    .addComponent(jScrollPane2, javax.swing.GroupLayout.DEFAULT_SIZE, 730, Short.MAX_VALUE)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addGap(10, 10, 10)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jLabel3)
                                .addGap(18, 18, 18)
                                .addComponent(txtID))
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jLabel6)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                                .addComponent(txtSSS, javax.swing.GroupLayout.PREFERRED_SIZE, 77, javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 6, Short.MAX_VALUE)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                            .addComponent(jLabel7)
                            .addComponent(jLabel4))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(txtFName)
                            .addComponent(txtpagIBIGLoan, javax.swing.GroupLayout.PREFERRED_SIZE, 75, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addGap(18, 18, 18)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jLabel8)
                                .addGap(18, 18, 18))
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jLabel9)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)))
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(layout.createSequentialGroup()
                                .addGap(9, 9, 9)
                                .addComponent(txtLName))
                            .addGroup(layout.createSequentialGroup()
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(txtpagIBIGContract, javax.swing.GroupLayout.PREFERRED_SIZE, 76, javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addGap(39, 39, 39)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jLabel5)
                                .addGap(18, 18, 18)
                                .addComponent(txtBaseSalary, javax.swing.GroupLayout.PREFERRED_SIZE, 85, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jLabel1)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(txtDateReceived, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addGap(35, 35, 35))
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 167, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(updateBtn)
                        .addGap(35, 35, 35)
                        .addComponent(removeBtn)
                        .addGap(31, 31, 31)
                        .addComponent(viewInactiveBtn)
                        .addGap(35, 35, 35)
                        .addComponent(cancelBtn)
                        .addGap(162, 162, 162)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel2)
                .addGap(18, 18, 18)
                .addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 92, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                        .addGroup(layout.createSequentialGroup()
                            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                                .addComponent(txtBaseSalary, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addComponent(jLabel5))
                            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(txtDateReceived, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addGroup(layout.createSequentialGroup()
                            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                                .addComponent(jLabel3)
                                .addComponent(txtID)
                                .addComponent(jLabel4)
                                .addComponent(txtFName))
                            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                .addGroup(layout.createSequentialGroup()
                                    .addGap(18, 18, 18)
                                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                                        .addComponent(jLabel6)
                                        .addComponent(txtSSS, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                                .addGroup(layout.createSequentialGroup()
                                    .addGap(18, 18, 18)
                                    .addComponent(txtpagIBIGLoan, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))))
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel9)
                            .addComponent(txtLName))
                        .addGap(18, 18, 18)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel8)
                            .addComponent(txtpagIBIGContract, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jLabel1)))
                    .addComponent(jLabel7))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 21, Short.MAX_VALUE)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(updateBtn)
                    .addComponent(cancelBtn)
                    .addComponent(removeBtn)
                    .addComponent(viewInactiveBtn))
                .addGap(51, 51, 51))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void cancelBtnMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_cancelBtnMouseClicked
        new AdminScreen2().setVisible(true);
        this.setVisible(false);
}//GEN-LAST:event_cancelBtnMouseClicked

    private void updateBtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_updateBtnActionPerformed
   
    }//GEN-LAST:event_updateBtnActionPerformed

    private void updateBtnMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_updateBtnMouseClicked
        String sql = "UPDATE salaryinfo SET FirstName = ?, LastName = ?, BaseSalary = ?, SSS = ?, PagIBIGLoan = ?, PagIBIGContract = ?, DateReceived = ? WHERE EmployeeID = ?";
        try{
            pst = conn.prepareStatement(sql);
            pst.setString(1, txtFName.getText());
            pst.setString(2, txtLName.getText());
            pst.setFloat(3, Float.parseFloat(txtBaseSalary.getText()));
            pst.setFloat(4, Float.parseFloat(txtSSS.getText()));
            pst.setFloat(5, Float.parseFloat(txtpagIBIGLoan.getText()));
            pst.setFloat(6, Float.parseFloat(txtpagIBIGContract.getText()));
            pst.setString(7, df.format(txtDateReceived.getDate()));
            pst.setInt(8, Integer.parseInt(txtID.getText()));
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully updated salary info!");
            UpdateJTable();
        }
        catch (Exception e){
           JOptionPane.showMessageDialog(null, e);
        }
    }//GEN-LAST:event_updateBtnMouseClicked

    private void viewInactiveBtnMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_viewInactiveBtnMouseClicked
        new ViewInactiveEmployeeSalary().setVisible(true);
        this.setVisible(false);
}//GEN-LAST:event_viewInactiveBtnMouseClicked

    private void viewInactiveBtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_viewInactiveBtnActionPerformed
        // TODO add your handling code here:
}//GEN-LAST:event_viewInactiveBtnActionPerformed

    private void removeBtnMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_removeBtnMouseClicked
        int response = JOptionPane.showConfirmDialog(null, "Are you sure you want to remove this employee?", "Confirm", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE);

        int empID = Integer.parseInt(txtID.getText());
        String empFname = (txtFName.getText());
        String empLname = (txtLName.getText());
        float empBSalary = (Float.parseFloat(txtBaseSalary.getText()));
        float empSSS = (Float.parseFloat(txtSSS.getText()));
        float empPgIBIGLoan = (Float.parseFloat(txtpagIBIGLoan.getText()));
        float empPgIBIGContract = (Float.parseFloat(txtpagIBIGContract.getText()));

        String empDept = (ViewEmployeeInfo.txtDept.getSelectedItem().toString());
        String empPosition = (ViewEmployeeInfo.txtPosition.getText());
        String empPassword = (ViewEmployeeInfo.txtPassword.getText());

        int month1 = txtDateReceived.getDate().getMonth() + 1;
        int year1 = txtDateReceived.getDate().getYear() + 1900;
        int day1 = txtDateReceived.getDate().getDate();

        if (response == JOptionPane.YES_OPTION) {
            try{
                stat1 = conn.createStatement();
                stat1.executeUpdate("INSERT INTO `medmartpayroll_db`.`inactive_employeeinfo` (`EmployeeID`, `FirstName`, `LastName`, `Position`, `Department`, `HiringDate`, `Password`) VALUES ('"+ empID +"', '"+ empFname +"', '"+ empLname +"', '"+ empDept +"', '"+ empPosition +"', '"+ year1 + "-"  + month1 + "-" + day1 +"', '"+ empPassword +"')");
                stat1.executeUpdate("DELETE FROM `medmartpayroll_db`.`employeeinfo` where `EmployeeID` = '" + empID + "'");
                stat1.executeUpdate("INSERT INTO `medmartpayroll_db`.`inactive_salaryinfo` (`EmployeeID`, `FirstName`, `LastName`, `BaseSalary`, `SSS`, `PagIBIGLoan`, `PagIBIGContract`, `DateReceived`) VALUES ('"+ empID +"', '"+ empFname +"', '"+ empLname +"', '"+ empBSalary +"', '"+ empSSS +"', '"+ empPgIBIGLoan +"', '"+ empPgIBIGContract +"', '"+ year1 + "-"  + month1 + "-" + day1 +"')");
                stat1.executeUpdate("DELETE FROM `medmartpayroll_db`.`salaryinfo` where `EmployeeID` = '" + empID + "'");
                UpdateJTable();
                JOptionPane.showMessageDialog(null, "Successfully removed salary info!");
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        else if (response == JOptionPane.CLOSED_OPTION) {
      System.out.println("JOptionPane closed");
      }
    }//GEN-LAST:event_removeBtnMouseClicked

    private void formWindowOpened(java.awt.event.WindowEvent evt) {//GEN-FIRST:event_formWindowOpened
        conn = Connector.get();
        UpdateJTable();
    }//GEN-LAST:event_formWindowOpened

    private void txtSSSActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtSSSActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_txtSSSActionPerformed

    private void jTable2MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jTable2MouseClicked
        int row = jTable2.getSelectedRow();
        txtID.setText(jTable2.getModel().getValueAt(row, 0).toString());
        txtFName.setText(jTable2.getModel().getValueAt(row, 1).toString());
        txtLName.setText(jTable2.getModel().getValueAt(row, 2).toString());
        txtBaseSalary.setText(jTable2.getModel().getValueAt(row, 3).toString());
        txtSSS.setText(jTable2.getModel().getValueAt(row, 4).toString());
        txtpagIBIGLoan.setText(jTable2.getModel().getValueAt(row, 5).toString());
        txtpagIBIGContract.setText(jTable2.getModel().getValueAt(row, 6).toString());
        try {
            txtDateReceived.setDateFormatString("yyyy-MM-dd");
            txtDateReceived.setDate(df.parse(jTable2.getModel().getValueAt(row, 7).toString()));
        } catch (ParseException ex) {
            Logger.getLogger(ViewEmployeeInfo.class.getName()).log(Level.SEVERE, null, ex);
        }
    }//GEN-LAST:event_jTable2MouseClicked

    private void txtpagIBIGLoanActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtpagIBIGLoanActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_txtpagIBIGLoanActionPerformed

    /**
    * @param args the command line arguments
    */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new ViewEmployeeSalary().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton cancelBtn;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JLabel jLabel8;
    private javax.swing.JLabel jLabel9;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JTable jTable1;
    private javax.swing.JTable jTable2;
    private javax.swing.JButton removeBtn;
    public static javax.swing.JTextField txtBaseSalary;
    public static com.toedter.calendar.JDateChooser txtDateReceived;
    private javax.swing.JLabel txtFName;
    private javax.swing.JLabel txtID;
    private javax.swing.JLabel txtLName;
    public static javax.swing.JTextField txtSSS;
    public static javax.swing.JTextField txtpagIBIGContract;
    public static javax.swing.JTextField txtpagIBIGLoan;
    private javax.swing.JButton updateBtn;
    private javax.swing.JButton viewInactiveBtn;
    // End of variables declaration//GEN-END:variables

}
